using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using SharpObjects.Configuration.ConfigurationProviders;
using SharpObjects.Data;
using System.Collections.Specialized;
using SharpObjects.Configuration;
using System.Data.SqlClient;
using System.Data;
using SharpObjects.Instantiation;
using SharpObjects.Interfaces;
using SharpObjects.Utils;
using IDataReader = System.Data.IDataReader;

namespace SharpObjects.Data.SqlServerDb
{
	public class SqlServerDatabase : IDb, IXmlInitializable
	{
		protected string id = null;
		protected string connectionString;
		protected SqlConnection connection;
		protected SqlTransaction transaction = null;

        #region Constructors

        public SqlServerDatabase()
		{
			this.connectionString = null;
			this.connection = null;
		}

        public SqlServerDatabase(string connectionString)
        {
            Initialize(connectionString);
        }

        #endregion // Constructors

        public string ResourceVersion { get { return "sql_server"; } }

		#region IXmlInitializable members

        /* 
            <database_descriptor id="TestDatabase1" assembly="SharpObjects.Data" type="SharpObjects.Data.SqlServerDb.SqlServerDatabase">
                            <connection_string>
                                <!-- "data source={0};initial catalog={1};user id={2};password={3};" -->
                                <parameter name="Initial Catalog">Test</parameter>
                                <parameter name="Data Source">(local)</parameter>
                                <parameter name="User">user1</parameter>
                                <parameter name="Password" is_encrypted="false">password1</parameter> 
                            </connection_string>
            </database_descriptor>
        */

        private class NameValuePair : IXmlInitializable
        {
            public string Name { get; set; }
            public string Value { get; set; }

            #region IXmlInitializable Members

            public void Initialize(XmlElement initializationElement)
            {
                XmlConfigurationProvider init = new XmlConfigurationProvider(initializationElement);

                this.Name = init.GetString("@name", init.GetString("@id", string.Empty));
                this.Value = initializationElement.InnerText;

                this.IsInitialized = true;
            }

            public bool IsInitialized { get; set; }

            #endregion

            public static readonly ITypeDescriptor TypeDescriptor = new TypeDescriptor()
                                                                        {
                                                                            AssemblyPath = "SharpObjects.Data",
                                                                            TypeName = typeof(NameValuePair).FullName,
                                                                            Type = typeof (NameValuePair)
                                                                        };
        }

		public void Initialize(System.Xml.XmlElement initializationElement)
		{
			XmlConfigurationProvider config = new XmlConfigurationProvider(initializationElement);
			this.id = config.GetString("@id", string.Empty);

			NameValuePair [] connparams = config.GetObjects<NameValuePair>("connection_string/parameter", NameValuePair.TypeDescriptor);
			StringBuilder sb = new StringBuilder();

			for(int k = 0; k < connparams.Length; k++)
			{
				string sParamName = connparams[k].Name;
				string sParamValue = connparams[k].Value;
				sb.AppendFormat("{0}={1};", sParamName, sParamValue);
			}

            Initialize(sb.ToString());

		    this.IsInitialized = true;
        }

        public bool IsInitialized { get; set; }
		
		#endregion // IXmlInitializable Members
		


		#region IDb Members

        public void Initialize(string connectionString)
        {
            this.connectionString = connectionString;
            this.connection = new SqlConnection(this.connectionString);
        }

		public IDataReader ExecuteReader(System.Data.IDbCommand cmd)
		{
			try
			{
				if(cmd.Connection != null)
				{
					if(cmd.Connection.State != System.Data.ConnectionState.Open)
					{
						cmd.Connection.Open();
					}

					return cmd.ExecuteReader();
				}
				else
				{
					throw new ArgumentException("Connection not set");
				}
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}

		public object ExecuteScalar(System.Data.IDbCommand cmd)
		{
			try
			{
				if(cmd.Connection != null)
				{
					if(cmd.Connection.State != System.Data.ConnectionState.Open)
					{
						try
						{
							cmd.Connection.Open();
							return cmd.ExecuteScalar();
						}
						finally
						{
							cmd.Connection.Close();
						}
					}
					else
					{
						return cmd.ExecuteScalar();
					}
				}
				else
				{
					throw new ArgumentException("Connection not set");
				}
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}

		public IDbConnection Connection
		{
			get
			{
				if(this.connection == null)
				{
					throw new ArgumentException("Connection not set");
				}

				return this.connection;
			}
		}

		public int ExecuteNonQuery(System.Data.IDbCommand cmd)
		{
			try
			{
				if(cmd.Connection != null)
				{
					if(cmd.Connection.State != System.Data.ConnectionState.Open)
					{
						try
						{
							cmd.Connection.Open();
							return cmd.ExecuteNonQuery();
						}
						finally
						{
							cmd.Connection.Close();
						}
					}
					else
					{
						return cmd.ExecuteNonQuery();
					}
				}
				else
				{
					throw new ArgumentException("Connection not set");
				}
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}

		public IDbCommand CreateSelectTopNQueryCommand(System.Data.IDbCommand select, int nCount)
		{
			string sSelect = string.Format("SELECT TOP ({1}) * FROM ({0}) AS tbl_{2}", select.CommandText, nCount, Guid.NewGuid().ToString("N"));
			return new SqlCommand(sSelect, select.Connection as SqlConnection, this.transaction);
		}

		public IDbCommand CreateSelectTopNQueryCommand(string sSelectStatement, int nCount)
		{
            string sSelect = string.Format("SELECT TOP ({1}) * FROM ({0}) AS tbl_{2}", sSelectStatement, nCount, Guid.NewGuid().ToString("N"));
			return new SqlCommand(sSelect, this.connection, this.transaction);
		}

		public IDbCommand CreateSelectTopNQueryCommand(string sTable, string sFieldList, string sWhere, string sEndOfQuery, int nCount)
		{
			string sSelect;

            if (sWhere != null && sWhere.Trim() != string.Empty)
            {
                sSelect = string.Format("SELECT TOP ({3}) {0} FROM {1} WHERE {2}  ", sFieldList, sTable, sWhere, nCount);
            }
            else
            {
                sSelect = string.Format("SELECT TOP ({2}) {0} FROM {1} ", sFieldList, sTable, nCount);
            }

			if(sEndOfQuery != null)
			{
				sSelect += sEndOfQuery;
			}

			return new SqlCommand(sSelect, this.connection, this.transaction);
		}

		public void SetCommandParameterValue(IDbCommand cmd, int nParamId, object value)
		{
			SqlParameter par = (SqlParameter)cmd.Parameters[nParamId];
			par.Value = value;
		}

		public void SetCommandParameterValue(IDbCommand cmd, string sParamName, object value)
		{
			SqlParameter par = (SqlParameter)cmd.Parameters[parameter_prefix + sParamName];
			par.Value = value; 
		}

		public IDbDataAdapter CreateDataAdapter(System.Data.IDbCommand SelectCmd, bool bGenerateAllCommands)
		{
			SqlDataAdapter da = new SqlDataAdapter(SelectCmd as SqlCommand);
            da.UpdateBatchSize = 0; // use maximum possible

			if(bGenerateAllCommands)
			{
				if(SelectCmd.Connection.State != System.Data.ConnectionState.Open)
				{
					try
					{
						SelectCmd.Connection.Open();
						// actual commands are created when da tries to use them
						SqlCommandBuilder builder = new SqlCommandBuilder(da);
					}
					finally
					{
						SelectCmd.Connection.Close();
					}
				}
				else
				{
					// actual commands are created when da tries to use them
					SqlCommandBuilder builder = new SqlCommandBuilder(da);
				}
			}

			return da;

		}

		public IDbDataAdapter CreateDataAdapter(System.Data.IDbCommand SelectCmd)
		{
			SqlDataAdapter da = new SqlDataAdapter(SelectCmd as SqlCommand);
            da.UpdateBatchSize = 0; // max possible
            return da;
		}

		public IDbDataAdapter CreateDataAdapter(string sSelectSql, bool bGenerateAllCommands)
		{
			SqlCommand SelectCmd = new SqlCommand(sSelectSql, this.connection);
			return CreateDataAdapter(SelectCmd, bGenerateAllCommands);
		}

		public IDbDataAdapter CreateDataAdapter(string sSelectSql)
		{
			SqlCommand SelectCmd = new SqlCommand(sSelectSql, this.connection);
			return CreateDataAdapter(SelectCmd);
		}

        public void SetDataAdapterUpdateBatchSize(IDbDataAdapter da, int batchSize)
        {
            SqlDataAdapter sda = (SqlDataAdapter)da;
            sda.UpdateBatchSize = batchSize;
        }
        
        public DataSet FillDataSet(List<IDbDataAdapter> adapters)
		{
			try
			{
				DataSet ds = new DataSet();

				if(this.connection.State != System.Data.ConnectionState.Open)
				{
					try
					{
						this.connection.Open();

						foreach(SqlDataAdapter da in adapters)
						{
							da.Fill(ds);
						}
					}
					finally
					{
						this.connection.Close();
					}
				}
				else
				{
					foreach(SqlDataAdapter da in adapters)
					{
						da.Fill(ds);
					}
				}

				return ds;
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}

		public DataSet FillDataSet(IDbDataAdapter adapter)
		{
			try
			{
				DataSet ds = new DataSet();
				adapter.Fill(ds);
				return ds;
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}

		public DataSet FillDataSet(List<IDbDataAdapter> adapters, StringCollection tables)
		{
			try
			{
				DataSet ds = new DataSet();

				if(this.connection.State != System.Data.ConnectionState.Open)
				{
					try
					{
						this.connection.Open();

						for(int k = 0; k < Math.Min(adapters.Count, tables.Count); k++)
						{
							SqlDataAdapter da = (SqlDataAdapter)adapters[k];
							da.Fill(ds, tables[k]);
						}
					}
					finally
					{
						this.connection.Close();
					}
				}
				else
				{
					for(int k = 0; k < Math.Min(adapters.Count, tables.Count); k++)
					{
						SqlDataAdapter da = (SqlDataAdapter)adapters[k];
						da.Fill(ds, tables[k]); 
					}
				}

				return ds;
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}

		public DataSet FillDataSet(IDbDataAdapter adapter, string sTable)
		{
			DataSet ds = new DataSet();
			SqlDataAdapter da = (SqlDataAdapter)adapter;
			da.Fill(ds, sTable);
			return ds;
		}

		public DataTable FillDataTable(IDbDataAdapter adapter)
		{
			try
			{
				DataTable dt = new DataTable();
				SqlDataAdapter da = (SqlDataAdapter)adapter;
				da.Fill(dt);
				return dt;
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}


		public void UpdateDataSet(IDbDataAdapter adapter, DataSet ds)
		{
			try
			{
				adapter.Update(ds);
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}

		public void UpdateDataSet(List<IDbDataAdapter> adapters, StringCollection tables, DataSet ds)
		{
			try
			{
				if(this.connection.State != System.Data.ConnectionState.Open)
				{
					try
					{
						this.connection.Open();

						for(int k = 0; k < Math.Min(adapters.Count, tables.Count); k++)
						{
							SqlDataAdapter da = (SqlDataAdapter)adapters[k];
							da.Update(ds, tables[k]);
						}
					}
					finally
					{
						this.connection.Close();
					}
				}
				else
				{
					for(int k = 0; k < Math.Min(adapters.Count, tables.Count); k++)
					{
						SqlDataAdapter da = (SqlDataAdapter)adapters[k];
						da.Update(ds, tables[k]); 
					}
				}
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}

		public void UpdateDataSet(IDbDataAdapter adapter, string sTable, DataSet ds)
		{
			try
			{
				SqlDataAdapter da = (SqlDataAdapter)adapter;
				da.Update(ds, sTable);
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}

		public void UpdateDataTable(IDbDataAdapter adapter, DataTable dt)
		{
			SqlDataAdapter da = (SqlDataAdapter)adapter;
			da.Update(dt);
		}

		public void UpdateDataRows(IDbDataAdapter adapter, DataRow [] rows)
		{
			try
			{
				SqlDataAdapter da = (SqlDataAdapter)adapter;
				da.Update(rows);
			}
			catch(SqlException ex)
			{
				throw new DataException(ex.Message, ex);
			}
		}

		protected const string parameter_prefix = "@";

		protected string [] GetParamNames(IDbParameterDescriptor [] parameters)
		{
			string [] param_strings = new string [parameters.Length];

			for(int k = 0; k < parameters.Length; k++)
			{
                if (parameters[k].IsDirectSqlExpression)
                {
                    param_strings[k] = parameters[k].Value.ToString();
                }
                else
                {
                    param_strings[k] = parameter_prefix + parameters[k].ToString();
                }
			}

			return param_strings;
		}

		public IDbCommand CreateQueryCommand(string sSqlTemplate, params IDbParameterDescriptor[] parameters)
		{
			string sSql = string.Format(sSqlTemplate, GetParamNames(parameters));
			SqlCommand cmd = new SqlCommand(sSql, this.connection, this.transaction);
			cmd.CommandType = CommandType.Text; // default

			foreach(DbParameterDescriptor pd in parameters)
			{
                if (!pd.IsDirectSqlExpression)
                {
                    SqlParameter par = new SqlParameter();
                    par.ParameterName = parameter_prefix + pd.ParameterName;
                    par.SqlDbType = DataTypeToSqlDbType(pd.EDataType);
                    par.Size = pd.Size;
                    par.SourceColumn = parameter_prefix + pd.SourceColumn;
                    par.Direction = pd.Direction;
                    par.Precision = pd.Precision;
                    par.Scale = pd.Scale;
                    par.SourceVersion = pd.SourceVersion;
                    par.Value = pd.Value;

                    cmd.Parameters.Add(par);
                }
			}
			
			return cmd;
		}

		public IDbCommand CreateQueryCommand(string sSqlTemplate, List<DbParameterDescriptor> parameters)
		{
			DbParameterDescriptor [] pars = new DbParameterDescriptor [parameters.Count];

			for(int k = 0; k < pars.Length; k++)
			{
				pars[k] = parameters[k];
			}

			return CreateQueryCommand(sSqlTemplate, pars);
		}

/*
	<param_descriptors>
		<param_descriptor name="PROCESS_ID" type="Int32" direction="Input" />
		<param_descriptor name="DESCRIPTION" type="String" direction="Input" nullable="true" precision="0" scale="0" size="200" source_column="DESCRIPTION" row_version="Current"/>
	</param_descriptors>
*/

		public IDbCommand CreateQueryCommand(string sSqlTemplate, string sParamDescriptorXml, params object[] values)
		{
            XmlConfigurationProvider paramDescriptors = new XmlConfigurationProvider(sParamDescriptorXml);
            DbParameterDescriptor[] pardescs = paramDescriptors.GetObjects<DbParameterDescriptor>("param_descriptor", DbParameterDescriptor.TypeDescriptor);

			for(int k = 0; k < pardescs.Length; k++)
			{
				pardescs[k].Value = values[k];
			}

			return CreateQueryCommand(sSqlTemplate, pardescs);
		}

		public IDbCommand CreateQueryCommand(string sSql)
		{
			SqlCommand cmd = new SqlCommand(sSql, this.connection, this.transaction);
			return cmd;
		}

/*
	ALTER PROCEDURE GET_STATUS

		(
			@nu_RECORD_ID INT,
			@vc_DATA VARCHAR OUTPUT
		)

	AS

*/

		public IDbProcedureDescriptor ParseProcedureDefinitionSql(string sProcDefSql)
		{
			IDbProcedureDescriptor desc = null;

			int nStartInd;
			int nOpenBracket;
			int nCloseBracket;
			bool bProcedure = true;

			nStartInd = sProcDefSql.ToLower().IndexOf("procedure");

			if(nStartInd >= 0)
			{
				nStartInd = nStartInd + "procedure".Length;
			}
			else
			{
				bProcedure = false;
				nStartInd = sProcDefSql.ToLower().IndexOf("function");

				if(nStartInd >= 0)
				{
					nStartInd = nStartInd + "function".Length;
				}
			}

			nOpenBracket = sProcDefSql.IndexOf("(");
			nCloseBracket = sProcDefSql.IndexOf(")");

			if(nStartInd == -1 || nOpenBracket == -1 || nCloseBracket == -1 || nStartInd > nOpenBracket || nCloseBracket < nOpenBracket)
			{
				throw new ArgumentException("Invalid SQL procedure / function definition format", sProcDefSql);
			}

			desc = new DbProcedureDescriptor();
			desc.ProcedureName = sProcDefSql.Substring(nStartInd, nOpenBracket - nStartInd).Trim();
			desc.ProcedureName = desc.ProcedureName.Replace("\"", "");

			string sParams = sProcDefSql.Substring(nOpenBracket + 1, nCloseBracket - nOpenBracket - 1).Trim();
			
			if(sParams.Length > 0)
			{
				desc.ParameterDescriptors = ParseParams(sParams);
			}

			if(! bProcedure)
			{
				AddReturnParam(desc.ParameterDescriptors, sProcDefSql, nCloseBracket);
			}

			return desc;
		}

		protected void AddReturnParam(List<DbParameterDescriptor> parameters, string sProcDefSql, int nCloseBracket)
		{
			DbParameterDescriptor pd = new DbParameterDescriptor();
			pd.ParameterName = "return";

			// get return param type
			string sReturn = sProcDefSql.Substring(nCloseBracket + 1).Trim();

			if(! sReturn.StartsWith("return"))
			{
				throw new ArgumentException("Return type must be specified for a function", sProcDefSql);
			}

			sReturn = sReturn.Substring("return".Length).Trim();
			int nInd = sReturn.IndexOf(" ");

			if(nInd != -1)
			{
				sReturn = sReturn.Substring(0, nInd).Trim();
			}

			pd.EDataType = GetParamType(sReturn);

			if(pd.EDataType == eDataType.AnsiString || pd.EDataType == eDataType.UnicodeString)
			{
				pd.Size = 2000;
			}

			parameters.Add(pd);
		}

/*
	ALTER PROCEDURE GET_STATUS

		(
			@nu_RECORD_ID INT,
			@vc_DATA VARCHAR OUTPUT
		)

	AS

*/
        private class DelimitedParamString
        {
            public char PairDelimiter = ';';
            public char ValueDelimiter = '=';
            public Dictionary<string, string> Params = new Dictionary<string, string>();

            public DelimitedParamString(string sParamString)
            {
                Parse(sParamString, PairDelimiter, ValueDelimiter);
            }

            public DelimitedParamString(string sParamString, char sPairDelimiter, char sValueDelimiter)
            {
                PairDelimiter = sPairDelimiter;
                ValueDelimiter = sValueDelimiter;

                Parse(sParamString, PairDelimiter, ValueDelimiter);
            }

            protected void Parse(string sParamString, char sPairDelimiter, char sValueDelimiter)
            {
                string[] pairs = sParamString.Split(new char[] { PairDelimiter });

                char[] val_delim = new char[] { ValueDelimiter };

                foreach (string sPair in pairs)
                {
                    string[] param = sPair.Trim().Split(val_delim, 2);

                    if (param.Length == 2)
                    {
                        Params[param[0].Trim()] = param[1].Trim();
                    }
                }
            }

        }

		protected List<DbParameterDescriptor> ParseParams(string sParams)
		{
			List<DbParameterDescriptor> parameters = new List<DbParameterDescriptor>();

			DelimitedParamString dsparams = new DelimitedParamString(sParams, ',', ' ');

			foreach(string sParamName in dsparams.Params.Keys)
			{
				DbParameterDescriptor pd = new DbParameterDescriptor();
				int nInd = sParamName.IndexOf("(");
		
				if(nInd < 0)
				{
					pd.ParameterName = sParamName;
				}
				else
				{
					pd.ParameterName = sParamName.Substring(0, nInd);
					string sSize = sParamName.Substring(nInd + 1);
					sSize = sSize.Substring(0, sSize.Length - 1).Trim();
					pd.Size = Convert.ToInt32(sSize);
				}

				string [] pair = ((string)dsparams.Params[sParamName]).Split(new char [] { ' ', '\t', '\n' } );

				if(pair.Length == 1)
				{
					pd.Direction = ParameterDirection.Input;
					pd.EDataType = GetParamType(pair[0]);

					if(pd.EDataType == eDataType.AnsiString || pd.EDataType == eDataType.UnicodeString)
					{
						pd.Size = 2000;
					}
				}
				else if(pair.Length == 2) // OUTPUT
				{
					pd.Direction = ParameterDirection.Output;
					pd.EDataType = GetParamType(pair[0]);

					if(pd.EDataType == eDataType.AnsiString || pd.EDataType == eDataType.UnicodeString)
					{
						pd.Size = 2000;
					}
				}

				parameters.Add(pd);
			}

			return parameters;
		}


		public IDbCommand CreateProcedureCommand(IDbProcedureDescriptor ProcDescriptor)
		{
			return CreateProcedureCommand(ProcDescriptor.ProcedureName, ProcDescriptor.ParameterDescriptors.ToArray());
		}

		public IDbCommand CreateProcedureCommand(string sProcName, params IDbParameterDescriptor[] parameters)
		{
			SqlCommand cmd = new SqlCommand(sProcName, this.connection, this.transaction);
			cmd.CommandType = CommandType.StoredProcedure;

			foreach(DbParameterDescriptor pd in parameters)
			{
				if((pd.ParameterName != null && pd.ParameterName != string.Empty) &&
					pd.EDataType != eDataType.Recordset)
				{
					SqlParameter par = new SqlParameter();

					if(pd.ParameterName.StartsWith(parameter_prefix))
					{
						par.ParameterName = pd.ParameterName;
					}
					else
					{
						par.ParameterName = parameter_prefix + pd.ParameterName;
					}

					par.SqlDbType = DataTypeToSqlDbType(pd.EDataType);
					par.Size = pd.Size;
					par.SourceColumn = pd.SourceColumn;
					par.Direction = pd.Direction;
					par.Precision = pd.Precision;
					par.Scale = pd.Scale;
					par.SourceVersion = pd.SourceVersion;
					par.Value = pd.Value;

					cmd.Parameters.Add(par);
				}
			}
			
			return cmd;
		}

        //public IDbCommand CreateProcedureCommand(string sProcName, DbParameterDescriptor[] parameters)
        //{
        //    DbParameterDescriptor [] pars = new DbParameterDescriptor [parameters.Length];

        //    for(int k = 0; k < pars.Length; k++)
        //    {
        //        pars[k] = parameters[k];
        //    }

        //    return CreateProcedureCommand(sProcName, pars);
        //}

		/*
			<param_descriptors>
				<param_descriptor name="@PROCESS_ID" type="Int" direction="Input" />
				<param_descriptor name="@DESCRIPTION" type="UnicodeString" direction="Input" nullable="true" precision="0" scale="0" size="200" source_column="@DESCRIPTION" row_version="Current"/>
			</param_descriptors>
		*/

		public IDbCommand CreateProcedureCommand(string sProcName, string sParamDescriptorXml, params object[] values)
		{
            XmlConfigurationProvider paramDescriptors = new XmlConfigurationProvider(sParamDescriptorXml);
            DbParameterDescriptor[] pardescs = paramDescriptors.GetObjects<DbParameterDescriptor>("param_descriptor", DbParameterDescriptor.TypeDescriptor);

            for (int k = 0; k < pardescs.Length; k++)
            {
                pardescs[k].Value = values[k];
            }

			return CreateProcedureCommand(sProcName, pardescs);
		}

		public string ToDbDateTimeString(DateTime dt)
		{
			return string.Format("to_date('{0}','DD-MON-YYYY HH24:MI:SS')", dt.ToString("dd-MMM-yyyy HH:mm:ss"));
		}

		public string CurrentGmtDateFunction // returns database dependent expression that refers to the current GMT date and time
		{ 
			get 
			{ 
				return "GETUTCDATE()"; 
			} 
		}

        public string CurrentDateFunction // returns database dependent expression that refers to the current date and time
        {
            get
            {
                return "GETDATE()";
            }
        }

        public string CurrentDatePlusDays(int nDays) // returns database dependent expression that adds specified number of days to the current date and time
		{ 
			return string.Format("DATEADD(day, {0}, GETDATE())", nDays); 
		}
 
		public string CurrentDatePlusSeconds(int nSeconds) // returns database dependent expression that adds specified number of seconds to the current date and time
		{
			return string.Format("DATEADD(second, {0}, GETDATE())", nSeconds); 
		}

        public DateTime MinDateTime()
        {
            return System.Data.SqlTypes.SqlDateTime.MinValue.Value;
        }

        public DateTime MaxDateTime()
        {
            return System.Data.SqlTypes.SqlDateTime.MaxValue.Value;
        }

		// relies on a table: id - bigint, identity=yes, PK; guid_high - bigint, guid_low - bigint
		public Int64 GenerateUniqueSequenceNumber(string sTableName)
		{
			if(this.connection.State != System.Data.ConnectionState.Open)
			{
				try
				{
					this.connection.Open();
					return CreateUniqueSequenceNumberInternal(sTableName);
				}
				finally
				{
					this.connection.Close();
				}
			}
			else
			{
				return CreateUniqueSequenceNumberInternal(sTableName);
			}
		}

		protected Int64 CreateUniqueSequenceNumberInternal(string sTableName)
		{
			Integer128 uid = new Integer128(Guid.NewGuid());

            string sSqlCommandTemplate = "insert into " + sTableName + " (guid_high, guid_low) values({0},{1});select id from " + sTableName + " where guid_high={2} and guid_low={3};delete from " + sTableName + " where guid_high={4} and guid_low={5}";

            List<DbParameterDescriptor> parameters = new List<DbParameterDescriptor>();
            parameters.Add(new DbParameterDescriptor("insGuidHigh", eDataType.Long, ParameterDirection.Input, 8, uid.High));
            parameters.Add(new DbParameterDescriptor("insGuidLow", eDataType.Long, ParameterDirection.Input, 8, uid.Low));
            parameters.Add(new DbParameterDescriptor("selGuidHigh", eDataType.Long, ParameterDirection.Input, 8, uid.High));
            parameters.Add(new DbParameterDescriptor("selGuidLow", eDataType.Long, ParameterDirection.Input, 8, uid.Low));
            parameters.Add(new DbParameterDescriptor("delGuidHigh", eDataType.Long, ParameterDirection.Input, 8, uid.High));
            parameters.Add(new DbParameterDescriptor("delGuidLow", eDataType.Long, ParameterDirection.Input, 8, uid.Low));

            IDbCommand cmd = this.CreateQueryCommand(sSqlCommandTemplate, parameters);

            //string sCmd = string.Format("insert into {2} (guid_high, guid_low) values({0}, {1});select id from {2} where guid_high={0} and guid_low={1};delete from {2} where guid_high={0} and guid_low={1}", uid.High, uid.Low, sTableName);
            //IDbCommand cmd = this.CreateQueryCommand(sCmd);

            object obj = this.ExecuteScalar(cmd);
			Int64 id = Convert.ToInt64(obj);

			return id;
		}

		protected eDataType GetParamType(string sSqlType)
		{
		    sSqlType = sSqlType.ToLower();
			eDataType type;

			if(sSqlType == "varchar")
			{
				type = eDataType.AnsiString;
			}
            else if(sSqlType == "nvarchar")
            {
                type = eDataType.UnicodeString;
            }
			else if(sSqlType == "char")
			{
				type = eDataType.Char;
			}
			else if(sSqlType == "float")
			{
				type = eDataType.Double;
			}
			else if(sSqlType == "int")
			{
				type = eDataType.Int;
			}
			else if(sSqlType == "long")
			{
				type = eDataType.Long;
			}
			else if(sSqlType == "datetime")
			{
				type = eDataType.DateTime;
			}
			else
			{
				throw new Exception("Unsupported data type: " + sSqlType);
			}

			return type;
		}

		public string GetDataType(eDataType type)
		{
			SqlDbType sqltype = DataTypeToSqlDbType(type);

			if(sqltype == SqlDbType.Variant)
			{
				return string.Empty;
			}
			else
			{
				return sqltype.ToString();
			}
		}

		public bool IsDbException(Exception ex)
		{
			if(ex is SqlException)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		#endregion // IDb Members

		protected SqlDbType DataTypeToSqlDbType(eDataType type)
		{
			switch(type)
			{
				case eDataType.Char: return SqlDbType.Char;
				case eDataType.UnicodeChar: return SqlDbType.NChar;
				case eDataType.AnsiString: return SqlDbType.VarChar;
				case eDataType.UnicodeString: return SqlDbType.NVarChar;
				case eDataType.AnsiStringFixedLength: return SqlDbType.Char;
				case eDataType.UnicodeStringFixedLength: return SqlDbType.NChar;
				case eDataType.Text: return SqlDbType.Text;
				case eDataType.UnicodeText: return SqlDbType.NText;
				case eDataType.Binary: return SqlDbType.Image;
				case eDataType.Boolean: return SqlDbType.Bit;
				case eDataType.Byte: return SqlDbType.TinyInt;
				case eDataType.DateTime: return SqlDbType.DateTime;
				case eDataType.Double: return SqlDbType.Float;
				case eDataType.Decimal: return SqlDbType.Decimal;
				case eDataType.Int: return SqlDbType.Int;
				case eDataType.Long: return SqlDbType.BigInt;
				case eDataType.Recordset: return SqlDbType.Variant;
                case eDataType.UniqueIdentifier: return SqlDbType.UniqueIdentifier;
                case eDataType.Xml: return SqlDbType.Xml;

				default: throw new ArgumentException("Unsupported data type", type.ToString());
			}
		}
		
		protected SqlDbType StringToSqlDbType(string sDataType)
		{
			eDataType type = (eDataType)Enum.Parse(typeof(eDataType), sDataType, true);
			return DataTypeToSqlDbType(type);
		}

		public IDbTransaction BeginTransaction()
		{
			if(this.transaction != null)
			{
				throw new DataException("Transaction already began");
			}

			if(this.connection == null)
			{
				throw new DataException("Connection must exist before transaction is created");
			}

			if(this.connection.State != ConnectionState.Open)
			{
				throw new DataException("Connection must be open before transaction is created");
			}

			return this.transaction = this.connection.BeginTransaction();
		}

		public void CommitTransaction()
		{
			if(this.transaction == null)
			{
				throw new DataException("Transaction does not exist");
			}

			this.transaction.Commit();
			this.transaction = null;
		}

		public void RollbackTransaction()
		{
			if(this.transaction == null)
			{
				throw new DataException("Transaction does not exist");
			}

			this.transaction.Rollback();
			this.transaction = null;
		}

		public IDbTransaction Transaction { get { return this.transaction; } }

		//#region IDisposable Members

		public void Dispose()
		{
			if(this.connection != null)
			{
				if(this.connection.State == ConnectionState.Open)
				{
					if(this.transaction != null)
					{
						this.transaction.Rollback();
					}
				}
				
				this.connection.Close();
			}
		}

		//#endregion

		//#region IXmlInitializable Members


		public string Id { get { return this.id; } set { this.id = value; } }

/*
 		<configuration_section>
			<connection_string>
				<parameter name="Data Source">oralocal</parameter>
				<parameter name="User">john</parameter>
				<parameter name="Password" encryption_key_ref="general_key">wANT18bcDFmnMC2BnhmXlw==</parameter>
			</connection_string>
		</configuration_section>

*/


		//#endregion

        public bool CanBulkCopy { get { return true; } }

        public long BulkCopy(string sDestinationTable, DataTable srcTable, int nTimeoutSec)
        {
            return this.BulkCopy(sDestinationTable, srcTable, 0, nTimeoutSec);
        }

        public long BulkCopy(string sDestinationTable, DataTable srcTable, int batchSize, int nTimeoutSec)
        {
            if (this.connection.State != System.Data.ConnectionState.Open)
            {
                try
                {
                    this.connection.Open();
                    return DoBulkCopy(sDestinationTable, srcTable, batchSize, nTimeoutSec);
                }
                finally
                {
                    this.connection.Close();
                }
            }
            else
            {
                return DoBulkCopy(sDestinationTable, srcTable, batchSize, nTimeoutSec);
            }
        }

        public long BulkCopy(string sDestinationTable, DataRowCollection rows, int nTimeoutSec)
        {
            return this.BulkCopy(sDestinationTable, rows, 0, nTimeoutSec);
        }

        public long BulkCopy(string sDestinationTable, DataRowCollection rows, int batchSize, int nTimeoutSec)
        {
            if (this.connection.State != System.Data.ConnectionState.Open)
            {
                try
                {
                    this.connection.Open();
                    return DoBulkCopy(sDestinationTable, rows, batchSize, nTimeoutSec);
                }
                finally
                {
                    this.connection.Close();
                }
            }
            else
            {
                return DoBulkCopy(sDestinationTable, rows, batchSize, nTimeoutSec);
            }
        }

        public long BulkCopy(string sDestinationTable, IDataReader reader, int nTimeoutSec)
        {
            return this.BulkCopy(sDestinationTable, reader, 0, nTimeoutSec);
        }

        public long BulkCopy(string sDestinationTable, IDataReader reader, int batchSize, int nTimeoutSec)
        {
            if (this.connection.State != System.Data.ConnectionState.Open)
            {
                try
                {
                    this.connection.Open();
                    return DoBulkCopy(sDestinationTable, reader, batchSize, nTimeoutSec);
                }
                finally
                {
                    this.connection.Close();
                }
            }
            else
            {
                return DoBulkCopy(sDestinationTable, reader, batchSize, nTimeoutSec);
            }
        }

        protected long m_nRowCnt = 0;

        protected void bulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            m_nRowCnt += e.RowsCopied;
        }

        protected long DoBulkCopy(string sDestinationTable, DataTable srcTable, int batchSize, int nTimeoutSec)
        {
            try
            {
                lock (this)
                {
                    SqlConnection conn = (SqlConnection)this.Connection;
                    SqlBulkCopy bulk = new SqlBulkCopy(conn);
                    bulk.NotifyAfter = srcTable.Rows.Count;
                    bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulk_SqlRowsCopied);
                    bulk.DestinationTableName = sDestinationTable;
                    bulk.BulkCopyTimeout = nTimeoutSec;
                    bulk.BatchSize = batchSize;

                    m_nRowCnt = 0;
                    bulk.WriteToServer(srcTable);

                    return m_nRowCnt;
                }
            }
            catch (SqlException ex)
            {
                throw new DataException(ex.Message, ex);
            }
        }

        protected long DoBulkCopy(string sDestinationTable, DataRowCollection rows, int batchSize, int nTimeoutSec)
        {
            try
            {
                DataRow[] rowarr = new DataRow[rows.Count];

                for (int k = 0; k < rows.Count; k++)
                {
                    rowarr[k] = rows[k];
                }

                lock (this)
                {
                    SqlConnection conn = (SqlConnection)this.Connection;
                    SqlBulkCopy bulk = new SqlBulkCopy(conn);
                    bulk.NotifyAfter = rows.Count;
                    bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulk_SqlRowsCopied);
                    bulk.DestinationTableName = sDestinationTable;
                    bulk.BulkCopyTimeout = nTimeoutSec;
                    bulk.BatchSize = batchSize;

                    m_nRowCnt = 0;
                    bulk.WriteToServer(rowarr);

                    return m_nRowCnt;
                }
            }
            catch (SqlException ex)
            {
                throw new DataException(ex.Message, ex);
            }
        }

        protected long DoBulkCopy(string sDestinationTable, IDataReader reader, int batchSize, int nTimeoutSec)
        {
            try
            {
                lock (this)
                {
                    SqlConnection conn = (SqlConnection)this.Connection;
                    SqlBulkCopy bulk = new SqlBulkCopy(conn);
                    bulk.NotifyAfter = 1;
                    bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulk_SqlRowsCopied);
                    bulk.DestinationTableName = sDestinationTable;
                    bulk.BulkCopyTimeout = nTimeoutSec;
                    bulk.BatchSize = batchSize;

                    m_nRowCnt = 0;
                    bulk.WriteToServer(reader);

                    return m_nRowCnt;
                }
            }
            catch (SqlException ex)
            {
                throw new DataException(ex.Message, ex);
            }
        }

        public string ParameterPrefix { get { return parameter_prefix; } }

    }
}
